﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using QL_LICHLAMVIEC.Models;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace QL_LICHLAMVIEC.Controllers
{
    public class PDaoTaoController : Controller
    {
        //
        // GET: /PDaoTao/

        public ActionResult Index()
        {
            return View();
        }
         [HttpPost]
        public ActionResult Index(HttpPostedFileBase file, FormCollection collection) 
        {
            if (ModelState.IsValid)
            {
                try
                {
                var filename = System.IO.Path.GetFileName(file.FileName);
                string path = HttpContext.Server.MapPath(@"~\Content\Upload\"); // duong dan vat ly
                if (System.IO.File.Exists(path + filename) == true)
                {
                    System.IO.File.Delete(path + filename);
                }
                file.SaveAs(path + filename);

                string vpath = HttpContext.Server.MapPath("~/Content/Upload/"); // duong dan ao

                OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + vpath + filename + ";Extended Properties=Excel 8.0");
                
                    OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
                    oconn.Open();
                    OleDbDataReader odr = ocmd.ExecuteReader();
                    //  string mstkb = "";
                    string So_Phong = "";
                    int Tiet = 0;
                    int So_Tiet = 0;
                    int MSNV = 0;
                    string Mon_hoc = "";
                    int Tuan_bd = 0;
                    int So_tuan = 0;
                    DateTime Ngay_dau_tuan;
                    DateTime Ngay_cuoi_tuan;
                    string Ngay_day = "";


                    while (odr.Read())
                    {

                        So_Phong = valid(odr, 0);
                        Tiet = int.Parse(valid(odr, 1));
                        So_Tiet = int.Parse(valid(odr, 2));
                        MSNV = int.Parse(valid(odr, 3));
                        Mon_hoc = valid(odr, 4);
                        Tuan_bd = int.Parse(valid(odr, 5));
                        So_tuan = int.Parse(valid(odr, 6));
                        Ngay_dau_tuan = DateTime.Parse(valid(odr, 7));
                        Ngay_cuoi_tuan = DateTime.Parse(valid(odr, 8));
                        Ngay_day = valid(odr, 9);

                        insertdataintosql(Guid.NewGuid().ToString(), So_Phong, Tiet, So_Tiet, MSNV, Mon_hoc, Tuan_bd, So_tuan, Ngay_dau_tuan, Ngay_cuoi_tuan, Ngay_day);

                    }
                    oconn.Close();
                    ViewData["msg"] = "Thời khoá biểu đã nhập vào thành công.";
                }
                catch
                {
                    ViewData["msg"] ="Lỗi xảy ra trong khi nhập.";

                }
               
            }

                return View();
          
        }
        protected string valid(OleDbDataReader myreader, int stval)//thay the cot rong bang 0
        {

            object val = myreader[stval];
            if (val != DBNull.Value)
                return val.ToString();
            else
                return Convert.ToString(0);
        }

        public void insertdataintosql(string mstbk, string So_Phong, int Tiet, int So_Tiet, int MSNV, string Mon_hoc, int Tuan_bd, int So_tuan,
        DateTime Ngay_dau_tuan, DateTime Ngay_cuoi_tuan, string Ngay_day)
        {
            MyEventsDataContext data = new MyEventsDataContext();
            var TKB = data.SP_THOIKHOABIEU(mstbk, So_Phong, Tiet, So_Tiet, MSNV, Mon_hoc, Tuan_bd, So_tuan, Ngay_dau_tuan, Ngay_cuoi_tuan, Ngay_day);
            data.SubmitChanges();


        }
    }
}
